package indi.zhifa.engine.core.excel;


import cn.hutool.core.util.ClassUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSON;
import indi.zhifa.engine.core.common.web.utils.SpringUtil;
import indi.zhifa.engine.core.excel.annotation.Excel;
import indi.zhifa.engine.core.excel.annotation.ExcelColumn;
import indi.zhifa.engine.core.excel.entity.*;
import indi.zhifa.engine.core.excel.entity.enums.EConstraintType;
import indi.zhifa.engine.core.excel.entity.enums.EExcelErrorCode;
import indi.zhifa.engine.core.excel.entity.exception.ExcelException;
import indi.zhifa.engine.core.excel.handler.*;
import indi.zhifa.engine.core.excel.util.ExcelUtils;

import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.util.*;


@Slf4j
public abstract class BaseSheetHandler {

    protected List<ExcelErrorInfo> mErrorInfos;

    public List<ExcelErrorInfo> getErrorInfos() {
        return mErrorInfos;
    }

    public String getErrMsg() {
        StringBuilder errMsg = new StringBuilder();
        if (!CollectionUtils.isEmpty(mErrorInfos)) {
            mErrorInfos.forEach(item -> {
                errMsg.append(item.getSheetName() + " ");

                if (item.getRow() > 0) {
                    errMsg.append("第" +
                            (item.getRow() + 1) + "行");
                }

                if (item.getCol() > 0) {
                    errMsg.append("，第" +
                            ExcelUtils.getCol(item.getCol()) + "列");
                }

                errMsg.append("，错误信息："
                        + item.getErrorInfo() + "<br/>");
            });
        } else {
            return null;
        }
        return errMsg.toString();
    }

    protected List<ExcelHeader> mHeaders;

    public List<ExcelHeader> getHeaders() {
        return mHeaders;
    }

    protected Map<String, HandlerInfo> mHeaderMap;

    public Map<String, HandlerInfo> getHeaderMap() {
        return mHeaderMap;
    }

    protected ExcelHandlers excelHandlers;
    protected ExcelColumnTreeNode root;
    protected Class<BaseExcelSheetRowData> mRowDataClass;
    protected Excel mExcelInfo;
    protected int mKeyCol;

    @Getter
    protected int dataCnt;

    protected int mRowBegin = 0;
    protected int mColBegin = 0;

    public static final int MAX_COL = 100;
    public static final int MAX_ROW = 1048576;


    protected BaseSheetHandler(Class<BaseExcelSheetRowData> pRowDataClass) {
        mErrorInfos = new ArrayList<>();
        mHeaders = new ArrayList<>();
        mHeaderMap = new HashMap<>();
        dataCnt = 0;
        mRowDataClass = pRowDataClass;
        mExcelInfo = pRowDataClass.getAnnotation(Excel.class);
    }


    public boolean analysisHeader(Sheet pSheet) {
        return analysisHeader(0, 0, pSheet);
    }

    /**
     * 解析表头
     * 此处要求的Excel格式必须是用户模式
     *
     * @param pSheet sheet对象
     * @return 是否解析成功
     */
    public boolean analysisHeader(int pRowBegin, int pColBegin, Sheet pSheet) {

        List<CellRangeAddress> cellRangeAddressList = pSheet.getMergedRegions();
        // 查找表头的第一个Cell
        int col = pColBegin;
        int row = pRowBegin;
        boolean isFindBeginCell = false;
        while (row < 10) {
            if (!isBeginCell(row, col, pSheet)) {
                if (row >= col) {
                    row++;
                    col = pColBegin;
                } else {
                    col++;
                }
            } else {
                isFindBeginCell = true;
                break;
            }
        }
        if (!isFindBeginCell) {
            ExcelErrorInfo errorInfo = new ExcelErrorInfo(EExcelErrorCode.HEADER_BEGIN_CAN_NOT_FIND,
                    "sheet " + pSheet.getSheetName() + " 无法找到表头");
            errorInfo.setSheetName(pSheet.getSheetName());
            return false;
        }
        mColBegin = col;
        Cell beginCell = findCell(pSheet, row, col);
        CellRangeAddress beginHeaderCellRangAddress = findCellRangeAddress(beginCell, pSheet);
        CellRangeAddress pickTopCellAddresses = beginHeaderCellRangAddress;

        mRowBegin = beginHeaderCellRangAddress.getLastRow() + 1;
        for (int theCol = beginCell.getColumnIndex(); theCol < MAX_COL; theCol++) {
            // 获得该列的第一行
            Cell topCell = findCell(pSheet, beginHeaderCellRangAddress.getFirstRow(), theCol);
            String name = findStringCell(pSheet, topCell);
            if (null != pickTopCellAddresses) {
                if (pickTopCellAddresses.isInRange(beginHeaderCellRangAddress.getFirstRow(), theCol)) {
                    // 在首行区域内，直接OK
                } else {
                    if (!StringUtils.hasLength(name)) {
                        break;
                    }
                }
            } else {
                if (!StringUtils.hasLength(name)) {
                    break;
                }
            }
            pickTopCellAddresses = findCellRangeAddress(topCell, pSheet);
            ExcelHeader header = new ExcelHeader();
            List<String> keys = header.getKeys();
            Cell curCell = null;
            for (int theRow = beginHeaderCellRangAddress.getFirstRow(); theRow <= beginHeaderCellRangAddress.getLastRow(); theRow++) {
                curCell = findCell(pSheet, theRow, theCol);
                CellRangeAddress cellAddresses = findCellRangeAddress(curCell, pSheet);
                if (null != cellAddresses) {
                    curCell = findCell(pSheet, cellAddresses.getFirstRow(), cellAddresses.getFirstColumn());
                    theRow = cellAddresses.getLastRow();
                }
                String theName = findStringCell(pSheet, curCell);
                if (StrUtil.isEmptyIfStr(theName)) {
                    String pureName = theName.split("\n")[0];
                    keys.add(pureName);
                }
            }
            CellRangeAddress finalCellAddresses = findCellRangeAddress(curCell, pSheet);
            if (null != finalCellAddresses && finalCellAddresses.getFirstColumn() < finalCellAddresses.getLastColumn()) {
                header.setMultiCol(true);
                header.setColBegin(finalCellAddresses.getFirstColumn());
                header.setColEnd(finalCellAddresses.getLastColumn());
                header.setCol(theCol);
                mHeaders.add(header);
            } else {
                header.setMultiCol(false);
                header.setCol(theCol);
                mHeaders.add(header);
            }
        }

        // 建立表头的树
        excelHandlers = SpringUtil.getBean(ExcelHandlers.class);
        root = new ExcelColumnTreeNode();
        Field[] fields = getFields();
        for (Field field : fields) {
            ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
            if (null != excelColumn) {
                String[] keys = excelColumn.keys();
                ExcelColumnTreeNode curNode = root;
                for (int i = 0; i < keys.length; i++) {
                    if (i < keys.length - 1) {
                        curNode = curNode.addKey(keys[i]);
                    } else {
                        HandlerInfo handlerInfo = new HandlerInfo();
                        Class handlerCls = excelColumn.handler();
                        if (handlerCls == IExcelCellHandler.class) {
                            Class fieldClass = field.getType();
                            if (fieldClass == String.class || fieldClass == String[].class) {
                                handlerCls = StringCellHandler.class;
                            } else if (fieldClass == Double.class || fieldClass == Double.TYPE || fieldClass == Double[].class || fieldClass == double[].class) {
                                handlerCls = DoubleCellHandler.class;
                            } else if (fieldClass == Float.class || fieldClass == Float.TYPE || fieldClass == Float[].class || fieldClass == float[].class) {
                                handlerCls = FloatCellHandler.class;
                            } else if (fieldClass == Integer.class || fieldClass == Integer.TYPE || fieldClass == Integer[].class || fieldClass == int[].class) {
                                handlerCls = IntegerCellHandler.class;
                            } else if (fieldClass == LocalDateTime.class) {
                                handlerCls = TimeCellHandler.class;
                            } else if (fieldClass == Boolean.class) {
                                handlerCls = BooleanCellHandler.class;
                            } else if (ClassUtil.isEnum(fieldClass) || ClassUtil.isEnum(fieldClass.getComponentType())) {
                                handlerCls = EnumCellHandler.class;
                            } else {
                                throw new ExcelException(EExcelErrorCode.UN_SUPPORT_HANDLER_TYPE, "该字段类型是" + fieldClass + " 不支持自动绑定处理器");
                            }
                        }
                        IExcelCellHandler handler = excelHandlers.getHandler(handlerCls);
                        if (null == handler) {
                            throw new ExcelException(EExcelErrorCode.UN_SUPPORT_HANDLER_TYPE, field.getName() + "上没有找到名为" + handlerCls + "的转换器");
                        }
                        handlerInfo.setHandler(handler);
                        handlerInfo.setField(field);
                        handlerInfo.setExcelColumn(excelColumn);
                        Set<String> strEnum = new HashSet<String>();
                        strEnum.addAll(Arrays.asList(excelColumn.strEnums()));
                        handlerInfo.setStrEnumMap(strEnum);
                        Set<Integer> intEnums = new HashSet<Integer>();
                        for (int intEnum : excelColumn.iEnums()) {
                            intEnums.add(intEnum);
                        }
                        handlerInfo.setIntEnumMap(intEnums);
                        curNode = curNode.addLeaf(keys[i], handlerInfo);
                        mHeaderMap.put(String.join("_", keys), handlerInfo);
                    }
                }
            }
        }

        // 对所有的ExcelHeader，找到树中对应的Handler，并赋值
        for (ExcelHeader excelHeader : mHeaders) {
            HandlerInfo handlerInfo = root.getExcelCellHandlerInfo(excelHeader);
            if (null != handlerInfo) {
                handlerInfo.setCol(excelHeader.getCol());
            }
        }

        if(null == mExcelInfo || mExcelInfo.keyCol().length == 0){
            mKeyCol = pRowBegin;
        }else{
            String headerKey = String.join("_", mExcelInfo.keyCol());
            HandlerInfo handlerInfo = mHeaderMap.get(headerKey);
            if(null == handlerInfo){
                throw new ExcelException(EExcelErrorCode.HEADER_COL_NOT_FIND,"没有找到名为 "+headerKey+" 的表头");
            }
            mKeyCol = handlerInfo.getCol();
        }
        return true;
    }

    /**
     * 初始化方法
     */
    public abstract void init();

    /**
     * 读取Sheet
     *
     * @param pSheet sheet对象
     * @param pMask  蒙版
     */
    public void readSheet(Sheet pSheet, Object pMask) {
        readSheet(0, 0, pSheet, pMask);
    }

    /**
     * 读取Sheet
     *
     * @param pSheet sheet对象
     * @param pMask  读取筛选的mask，具体逻辑由子类处理
     */
    public void readSheet(int pRowBegin, int pColBegin, Sheet pSheet, Object pMask) {
        init();
        if (!analysisHeader(pRowBegin, pColBegin, pSheet)) {
            return;
        }
        FormulaEvaluator evaluator = pSheet.getWorkbook().getCreationHelper().createFormulaEvaluator();

        for (int i = mRowBegin; i < MAX_ROW; i++) {
            try {
                Cell sentryCell = findCell(pSheet, i, mKeyCol);
                if (null == sentryCell || sentryCell.getCellType() == CellType.BLANK) {
                    break;
                }
                BaseExcelSheetRowData rowData;
                try {
                    rowData = createRowData();
                } catch (Exception ex) {
                    ExcelErrorInfo errorInfo = new ExcelErrorInfo(EExcelErrorCode.EXCEL_ROW_DATA_CREATE_ERR, "ExcelRowData创建错误");
                    mErrorInfos.add(errorInfo);
                    return;
                }
                rowData.init();
                for (int j = 0; j < mHeaders.size(); j++) {
                    ExcelHeader header = mHeaders.get(j);
                    try {
                        List<Cell> cells = new ArrayList<>();
                        if (header.isMultiCol()) {
                            int colBegin = header.getColBegin();
                            int colEnd = header.getColEnd();
                            for (int col = colBegin; col <= colEnd; col++) {
                                Cell cell = findCell(pSheet, i, col);
                                if (null != cell && cell.getCellType() == CellType.FORMULA) {
                                    cell = evaluator.evaluateInCell(cell);
                                }
                                cells.add(cell);
                            }
                        } else {
                            int col = header.getCol();
                            Cell cell = findCell(pSheet, i, col);
                            if (null != cell && cell.getCellType() == CellType.FORMULA) {
                                cell = evaluator.evaluateInCell(cell);
                            }
                            cells.add(cell);
                        }
                        if (!CollectionUtils.isEmpty(cells)) {
                            fillData(rowData, cells, header, pSheet);
                        }
                    } catch (IllegalStateException ex) {
                        ExcelErrorInfo errorInfo = new ExcelErrorInfo(pSheet.getSheetName(), i, j, EExcelErrorCode.CELL_TYPE_FAILED, "单元格类型错误");
                        mErrorInfos.add(errorInfo);
                    } catch (ExcelException ex) {
                        ExcelErrorInfo errorInfo = new ExcelErrorInfo(pSheet.getSheetName(), i, j, ex.getErrCode(), ex.getErrorInfo());
                        mErrorInfos.add(errorInfo);
                    } catch (Exception ex) {
                        ExcelErrorInfo errorInfo = new ExcelErrorInfo(pSheet.getSheetName(), i, j, EExcelErrorCode.UN_KNOW, "发生了未知错误" + ex);
                        mErrorInfos.add(errorInfo);
                    }
                }
                try {
                    rowData.finish();
                } catch (ExcelException ex) {
                    ExcelErrorInfo errorInfo = new ExcelErrorInfo(pSheet.getSheetName(), i, ex.getErrCode(), ex.getErrorInfo());
                    mErrorInfos.add(errorInfo);
                }
                addExcelSheetRowData(rowData, pMask);
            } catch (ExcelException ex) {
                ExcelErrorInfo errorInfo = new ExcelErrorInfo(pSheet.getSheetName(), i, 0, ex.getErrCode(), ex.getErrorInfo());
                mErrorInfos.add(errorInfo);
            }
        }
    }

    /**
     * 添加一个BaseExcelSheetRowData的回调
     *
     * @param pExcelSheetRowData
     * @param pMask
     */
    public abstract void addExcelSheetRowData(BaseExcelSheetRowData pExcelSheetRowData, Object pMask);

    /**
     * 从外部加载数据到内存
     *
     * @param pMask 加载筛选的mask，具体逻辑由子类处理
     */
    public abstract void loadData(Object pMask);

    /**
     * 导出Excel
     *
     * @param pSheet 要导出到的sheet
     * @throws IllegalAccessException
     */
    public abstract void export(Sheet pSheet) throws IllegalAccessException;

    protected void fillData(BaseExcelSheetRowData pRowData, List<Cell> pCellList, ExcelHeader pHeader, Sheet pSheet) throws IllegalAccessException {
        if (fillSimpleColumnData(pRowData, pCellList, pHeader, pSheet)) {
            fillOtherColumnData(pRowData, pCellList, pHeader);
        }
    }

    protected boolean fillSimpleColumnData(BaseExcelSheetRowData pRowData, List<Cell> pCellList, ExcelHeader pHeader, Sheet pSheet) throws IllegalAccessException {
        HandlerInfo handlerInfo = root.getExcelCellHandlerInfo(pHeader);
        if (null != handlerInfo) {
            ExcelColumn excelColumn = handlerInfo.getExcelColumn();
            IExcelCellHandler handler = handlerInfo.getHandler();
            Field field = handlerInfo.getField();
            if (null == handler) {
                log.error(String.join("-", pHeader.getKeys()) + " 没有handler");
                return false;
            }
            List<Object> results = new ArrayList<>();
            for (int i = 0; i < pCellList.size(); i++) {
                Cell theCell = pCellList.get(i);
                if (excelColumn.rowSpan()) {
                    if (CellType.BLANK == theCell.getCellType() || CellType._NONE == theCell.getCellType()) {
                        CellRangeAddress cellAddresses = findCellRangeAddress(theCell, pSheet);
                        if (null != cellAddresses) {
                            theCell = findCell(pSheet, cellAddresses.getFirstRow(), cellAddresses.getFirstColumn());
                        }
                    }
                }

                Object obj = handler.handle(theCell, field.getType());
                if (null != obj) {
                    // 校验
                    if (excelColumn.constraintType() == EConstraintType.STR_ENUM) {
                        // 字符串枚举
                        Set<String> strEnums = handlerInfo.getStrEnumMap();
                        if (StrUtil.isEmptyIfStr(obj) && !strEnums.contains(obj)) {
                            throw new ExcelException(EExcelErrorCode.CONSTRAINT_VIOLATE,
                                    "单元格值为【" + obj + "】，单元格枚举约束为" + JSON.toJSONString(excelColumn.strEnums()));
                        }
                    } else if (excelColumn.constraintType() == EConstraintType.INT_ENUM) {
                        // 整型枚举
                        Set<Integer> intEnums = handlerInfo.getIntEnumMap();
                        if (!intEnums.contains(obj)) {
                            throw new ExcelException(EExcelErrorCode.CONSTRAINT_VIOLATE,
                                    "单元格值为【" + obj + "】，单元格枚举约束为" + JSON.toJSONString(excelColumn.strEnums()));
                        }
                    } else if (excelColumn.constraintType() == EConstraintType.RANG) {
                        // 范围
                        double testV = (double) obj;
                        if (!(testV >= excelColumn.min() && testV <= excelColumn.max())) {
                            throw new ExcelException(EExcelErrorCode.CONSTRAINT_VIOLATE,
                                    "单元格值为【" + obj + "】，枚举约束范围是" + excelColumn.min() + "~" + excelColumn.max());
                        }
                    }
                    results.add(obj);
                } else {
                    if (!excelColumn.nullable()) {
                        throw new ExcelException(EExcelErrorCode.CELL_NO_EMPTY, "单元格为空，不符合定义");
                    }
                }
            }

            Class cls = field.getType();
            if (cls.isArray()) {
                field.setAccessible(true);
                Object[] arrData = (Object[]) field.get(pRowData);
                for (int i = 0; i < results.size() && i < arrData.length; i++) {
                    arrData[i] = results.get(i);
                }
            } else {
                if (results.size() > 0) {
                    Object theObj = results.get(0);
                    field.setAccessible(true);
                    field.set(pRowData, theObj);
                } else {
                    field.setAccessible(true);
                    field.set(pRowData, null);
                }
            }

            return true;
        }
        return false;
    }

    protected void fillOtherColumnData(BaseExcelSheetRowData pRowData, List<Cell> pCellList, ExcelHeader pHeader) {
        dataCnt++;
        pRowData.addCell(pCellList, pHeader);
    }

    protected BaseExcelSheetRowData createRowData() throws InstantiationException, IllegalAccessException {
        return mRowDataClass.newInstance();
    }

    protected HandlerInfo getHeader(String[] pHeaderColumns) {
        String key = String.join("_", pHeaderColumns);
        HandlerInfo excelHeader = mHeaderMap.get(key);
        return excelHeader;
    }

    protected void fillRow(Sheet pSheet, Class rowDataClass, BaseExcelSheetRowData pRowData, int pRowIdx) {
        Field[] fields = rowDataClass.getDeclaredFields();
        for (Field field : fields) {
            ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
            if (null == excelColumn) {
                continue;
            }
            HandlerInfo excelHeader = getHeader(excelColumn.keys());
            if (null == excelHeader) {
                continue;
            }
            Cell cell = getOrCreateCell(pSheet, mRowBegin + pRowIdx, excelHeader.getCol());
            field.setAccessible(true);

            try {
                excelHeader.getHandler().fill(cell, pRowData, field);
            } catch (Exception ex) {
                throw new ExcelException(EExcelErrorCode.REFLEX_ERROR,
                        "反射错误，名为" + field.getName() + " 类型为 " + field.getType() + " 的数据反射出现错误,错误信息是：" + ex.toString() + "，数据详情：\n" + JSON.toJSONString(pRowData));
            }

        }
    }

    protected Cell getOrCreateCell(Sheet pSheet, int pRow, int pCol) {
        Row row = pSheet.getRow(pRow);
        if (null == row) {
            row = pSheet.createRow(pRow);
        }
        Cell cell = row.getCell(pCol);
        if (null == cell) {
            cell = row.createCell(pCol);
        }
        return cell;
    }

    /**
     * 重载该Sheet的行数据有哪些字段
     *
     * @return
     */
    protected Field[] getFields() {
        List<Field> fields = new ArrayList<>();
        Class cls = mRowDataClass;
        while (null != cls) {
            fields.addAll(Arrays.asList(cls.getDeclaredFields()));
            cls = cls.getSuperclass();
        }
        return fields.toArray(new Field[0]);
    }


    private Cell findCell(Sheet pSheet, int pRow, int pCol) {
        Row row = pSheet.getRow(pRow);
        if (null == row) {
            return null;
        }
        return row.getCell(pCol);
    }

    private String findStringCell(Sheet pSheet, Cell pCell) {
        if (null == pCell) {
            return null;
        }
        if (pCell.getCellType() != CellType.STRING) {
            return null;
        }
        return pCell.getStringCellValue();
    }

    private boolean isBeginCell(int pRow, int pCol, Sheet pSheet) {
        Cell cell = findCell(pSheet, pRow, pCol);
        String name = findStringCell(pSheet, cell);
        if (!StrUtil.isEmptyIfStr(name)) {
            return false;
        }
        CellRangeAddress cellAddresses = findCellRangeAddress(cell, pSheet);
        return cellAddresses != null;
    }

    private CellRangeAddress findCellRangeAddress(Cell pCell, Sheet pSheet) {
        List<CellRangeAddress> cellRangeAddressList = pSheet.getMergedRegions();
        for (CellRangeAddress cellAddresses : cellRangeAddressList) {
            if (cellAddresses.isInRange(pCell)) {
                return cellAddresses;
            }
        }
        return null;
    }

}
